﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using ZHDJ.Core;

namespace ZHDJ.Data.Models.Edu
{
    /// <summary>
    /// 课程课件对应表
    /// </summary>
    public class PartyeduLessonCourseRelation : EntityBase
    {
        #region 字段属性

        public string LessonId { get; set; }

        public string CoursewareId { get; set; }

        public int SortNo { get; set; }
        #endregion

        #region 数据
        public static DataSet GetPartyEduCourseware(int pageIndex, int pageSize, string category, string courseType, string searchKey = "",string status="")
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "Begin",Value = (pageIndex)*pageSize},
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "PageSize",Value = pageSize},
                new MySqlParameter(){DbType = DbType.String,ParameterName = "CategoryDD",Value = SysDataDictionary.EduThemeCategory},
                new MySqlParameter(){DbType = DbType.String,ParameterName = "CourseTypeDD",Value = SysDataDictionary.EduCourseType}
            };
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select SQL_CALC_FOUND_ROWS  a.*,d1.DDicMemberName as CategoryName,d2.DDicMemberName as CourseTypeName,
                  
                    if(a.Status=4,'已发布','草稿') as StatusName,(select FileID from tblpartyeducoursewareimage m where m.CoursewareID=a.id order by FileType desc limit 1 ) ImageID
                    from tblPartyEduCourseware a
                    left join tbldatadictionary d1 on (d1.DDicType=?CategoryDD and a.Category=d1.DDicMember)
                    left join tbldatadictionary d2 on (d2.DDicType=?CourseTypeDD and a.CourseType=d2.DDicMember)
                    where 1=1 ");
            if (!string.IsNullOrEmpty(searchKey))
            {
                sb.Append(" and a.Title like ?key ");
                parameters.Add(
                    new MySqlParameter(){DbType = DbType.String,ParameterName = "key",Value = string.Format("%{0}%", searchKey)});
            }
            if (!string.IsNullOrEmpty(status))
            {
                sb.Append(" and a.status = ?status ");
                parameters.Add(
                    new MySqlParameter() { DbType = DbType.Int32, ParameterName = "status", Value = Convert.ToInt32(status)});
            }
            if (!string.IsNullOrEmpty(category))
            {
                sb.Append(" and a.Category=?Category");
                parameters.Add(new MySqlParameter(){DbType = DbType.String,ParameterName = "Category",Value = category});
            }
            if (!string.IsNullOrEmpty(courseType))
            {
                sb.Append(" and a.CourseType=?CourseType");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "CourseType", Value = courseType });
            }
            sb.Append(" order by a.Status desc, a.Flags desc, a.CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }
        #endregion

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static DataTable GetSingle(string id)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.String,ParameterName = "id",Value = id},
                new MySqlParameter(){DbType = DbType.String,ParameterName = "CategoryDD",Value = SysDataDictionary.EduThemeCategory},
                new MySqlParameter(){DbType = DbType.String,ParameterName = "CourseTypeDD",Value = SysDataDictionary.EduCourseType}
            };
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select a.*,d1.DDicMemberName as CategoryName,d2.DDicMemberName as CourseTypeName,
                    if(a.Status=4,'已发布','草稿') as StatusName from tblPartyEduCourseware a 
left join tbldatadictionary d1 on (d1.DDicType=?CategoryDD and a.Category=d1.DDicMember)
                    left join tbldatadictionary d2 on (d2.DDicType=?CourseTypeDD and a.CourseType=d2.DDicMember)
where a.id=?id ");

            return MySqlHelper.ExecuteDataTable(sb.ToString(), parameters.ToArray());
        }

        /// <summary>
        /// 获取视频点击排行
        /// </summary>
        /// <returns></returns>
        public static DataTable GetEduCourceClickCountRankList() {

            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.String,ParameterName = "id",Value = ""},
                new MySqlParameter(){DbType = DbType.String,ParameterName = "CategoryDD",Value = SysDataDictionary.EduThemeCategory},
                new MySqlParameter(){DbType = DbType.String,ParameterName = "CourseTypeDD",Value = SysDataDictionary.EduCourseType}
            };
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select Title,ID,ClickCount from tblpartyeducourseware order by ClickCount DESC LIMIT 4;");

            return MySqlHelper.ExecuteDataTable(sb.ToString(), parameters.ToArray());
        }

        /// <summary>
        /// 获取视频点击排行
        /// </summary>
        /// <returns></returns>
        public static DataTable GetEduCourceCreateDateRankList()
        {

            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.String,ParameterName = "id",Value = ""},
                new MySqlParameter(){DbType = DbType.String,ParameterName = "CategoryDD",Value = SysDataDictionary.EduThemeCategory},
                new MySqlParameter(){DbType = DbType.String,ParameterName = "CourseTypeDD",Value = SysDataDictionary.EduCourseType}
            };
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select Title,ID,CreateTime from tblpartyeducourseware order by CreateTime DESC LIMIT 4;");

            return MySqlHelper.ExecuteDataTable(sb.ToString(), parameters.ToArray());
        }
    }
}
